package com.example.readexcel2;

import android.net.Uri;
import android.os.Bundle;
import android.app.Activity;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import android.app.Activity;
import android.content.Context;
import android.content.Intent;
import android.database.Cursor;
import android.os.Bundle;
import android.os.Environment;
import android.util.Log;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Toast;



public class MainActivity extends Activity implements OnClickListener{
    /** Called when the activity is first created. */
	DBAdapter myDb;
    ArrayList<FileExcel> kqdoc=new ArrayList<FileExcel>();
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        openDB();
       // Cursor cursor = myDb.getAllRows();
        if(readExcelFile(this,"myExcel.xls")==null){
        	 saveExcelFile(this,"myExcel.xls");
        }
       
       // View writeExcelButton = findViewById(R.id.writeExcel);
      //  writeExcelButton.setOnClickListener(this);
        View readExcelButton = findViewById(R.id.readExcel);
        readExcelButton.setOnClickListener(this);
        	
    }
    private void openDB() {
		myDb = new DBAdapter(this);
		myDb.open();
	}
	@Override
	public void onDestroy() {
		super.onDestroy();	
		closeDB();
		
	}
	private void closeDB() {
		myDb.close();
	}
    public void onClick(View v) {
        switch (v.getId()) {
        /*
        case R.id.writeExcel:
            saveExcelFile(this,"myExcel.xls");
            break;
            */
        case R.id.readExcel:
           kqdoc=readExcelFile(this,"myExcel.xls");
           //so hang la bao gom tieu denhe
           for(int i=1;i<kqdoc.size();i++){
        	  
        	  String tenSanPham=kqdoc.get(i).getItem();
        	  String soLuong=kqdoc.get(i).getPri();
        	  String giaTien=kqdoc.get(i).getQua();
        	  Log.w("FileUtils", "Giá trị [i]: " + giaTien );
        	  myDb.insertRowDBLove(tenSanPham, soLuong, giaTien);// chen du lieu file vao DB
                   }
           closeDB();
           //Toast.makeText(MainActivity.this,"So hang doc dc: "+kqdoc.size(),5000).show();
           Intent i = new Intent(this,Singlelist.class);
   		   startActivity(i);
            break;   
        }
    }
 

    private static boolean saveExcelFile(Context context, String fileName) { 
 
        // check if available and not read only 
        if (!isExternalStorageAvailable() || isExternalStorageReadOnly()) { 
            Log.w("FileUtils", "Storage not available or read only"); 
            return false; 
        } 
 
        boolean success = false; 
 
        //New Workbook
        Workbook wb = new HSSFWorkbook();
        Cell c = null;
 
        //Cell style for header row
        CellStyle cs = wb.createCellStyle();
        cs.setFillForegroundColor(HSSFColor.LIME.index);
        cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        
        //New Sheet
        Sheet sheet1 = null;
        sheet1 = wb.createSheet("myOrder");
 
        // Generate column headings
        Row row = sheet1.createRow(0);
 
        c = row.createCell(0);
        c.setCellValue("Item Number");
        c.setCellStyle(cs);
 
        c = row.createCell(1);
        c.setCellValue("Quantity");
        c.setCellStyle(cs);
 
        c = row.createCell(2);
        c.setCellValue("Price");
        c.setCellStyle(cs);
 
        sheet1.setColumnWidth(0, (15 * 500));
        sheet1.setColumnWidth(1, (15 * 500));
        sheet1.setColumnWidth(2, (15 * 500));
 
        // Create a path where we will place our List of objects on external storage 
        File file = new File(context.getExternalFilesDir(null), fileName); 
        FileOutputStream os = null; 
 
        try { 
            os = new FileOutputStream(file);
            wb.write(os);
            Log.w("FileUtils", "Writing file" + file); 
            success = true; 
        } catch (IOException e) { 
            Log.w("FileUtils", "Error writing " + file, e); 
        } catch (Exception e) { 
            Log.w("FileUtils", "Failed to save file", e); 
        } finally { 
            try { 
                if (null != os) 
                    os.close(); 
            } catch (Exception ex) { 
            } 
        } 
 
        return success; 
    } 
    
    private static ArrayList<FileExcel> readExcelFile(Context context, String filename) { 
    	
    	ArrayList<FileExcel> d=new ArrayList<FileExcel>();
        if (!isExternalStorageAvailable() || isExternalStorageReadOnly()) 
        { 
            Log.w("FileUtils", "Storage not available or read only"); 
            return null; 
        } 
 
        try{
        	
            // Creating Input Stream 
            File file = new File(context.getExternalFilesDir(null), filename); 
            FileInputStream myInput = new FileInputStream(file);
 
            // Create a POIFSFileSystem object 
            POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
 
            // Create a workbook using the File System 
            HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
 
            // Get the first sheet from workbook 
            HSSFSheet mySheet = myWorkBook.getSheetAt(0);
 
            /** We now need something to iterate through the cells.**/
            Iterator<Row> rowIter = mySheet.rowIterator();
 
            while(rowIter.hasNext()){
            	HSSFRow myRow = (HSSFRow) rowIter.next();
                Iterator<Cell> cellIter = myRow.cellIterator();
                ArrayList<String> doc= new ArrayList<String>();
                while(cellIter.hasNext()){
                    HSSFCell myCell = (HSSFCell) cellIter.next();
                   // Log.w("FileUtils", "Giá trị i: " +  i);
                 //   Log.w("FileUtils", "Giá trị cột: " +  myCell.toString());// in trên màn hình logcat
                  //  Toast.makeText(context, "cell Value: " + myCell.toString(), Toast.LENGTH_SHORT).show();
                    doc.add(myCell.toString());
                    
                }
                FileExcel fileExcel=new FileExcel(doc.get(0),doc.get(1),doc.get(2));
               /*
                fileExcel.setItem(doc.get(0));
                fileExcel.setPri(doc.get(1));
                fileExcel.setQua(doc.get(2));
               */
                d.add(fileExcel);
                
            }
        }catch (Exception e){e.printStackTrace(); return null; }
		return d;
    } 
 
    public static boolean isExternalStorageReadOnly() { 
        String extStorageState = Environment.getExternalStorageState(); 
        if (Environment.MEDIA_MOUNTED_READ_ONLY.equals(extStorageState)) { 
            return true; 
        } 
        return false; 
    } 
 
    public static boolean isExternalStorageAvailable() { 
        String extStorageState = Environment.getExternalStorageState(); 
        if (Environment.MEDIA_MOUNTED.equals(extStorageState)) { 
            return true; 
        } 
        return false; 
    } 
}
